﻿using System;
using System.Collections.Generic;
using System.Text;
using Wangzeran.CodeMaker.Core.DB;
using Wangzeran.CodeMaker.Core.Model;
using System.Data.SqlClient;
using System.Data;

namespace Wangzeran.CodeMaker.PlugIns.DB.SQLServer2005
{
    /// <summary>
    /// SQL Server数据库架构操作
    /// </summary>
    public class SQLServerSchema : SQLServerUtility, IDBSchema
    {
        #region IDBSchema 成员
        /// <summary>
        /// 获取数据库集合
        /// </summary>
        /// <returns></returns>
        public IDictionary<string, DatabaseModal> GetDatabases()
        {
            try
            {
                IDictionary<string, DatabaseModal> lstDatabases = new Dictionary<string, DatabaseModal>();

                string sql = String.Format("select name from sys.databases");

                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase("master");
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        DatabaseModal database = new DatabaseModal();
                        database.DatabaseName = reader[0].ToString();
                        lstDatabases.Add(database.DatabaseName, database);
                    }
                    reader.Close();
                }

                return lstDatabases;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// 获取该数据库的所有表名
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <returns></returns>
        public IList<string> GetTableNames(string databaseName)
        {
            try
            {
                IList<string> lstTables = new List<string>();

                string sql = String.Format("select name from sysobjects where type='V' or type='U' order by name asc");
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    // 读取器
                    SqlDataReader reader = cmd.ExecuteReader();
                    IDictionary<string, string> dicDescription = null;

                    while (reader.Read())
                    {
                        lstTables.Add(reader[0].ToString());
                    }
                    reader.Close();
                }

                return lstTables;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        
        }

        /// <summary>
        /// 获取该数据库的相关表的详细信息集合
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <param name="tableNames">表名集合</param>
        /// <returns></returns>
        public IDictionary<string, TableModal> GetTables(string databaseName, string[] tableNames)
        {

            try
            {
                IDictionary<string, TableModal> lstTables = new Dictionary<string, TableModal>();

                // 表描述
                //string dicDescription = null;
                //foreach (string tableName in tableNames)
                //{
                //    TableModal table = new TableModal();
                //    table.TableName = tableName;
                //    table.DatabaseName = databaseName;
                //    // 表描述
                //    dicDescription = GetTableDescription(databaseName, table.TableName);
                //    table.TableDescription = dicDescription == null
                //        ? dicDescription.ToString() :
                //        null;
                //    lstTables.Add(table.TableName, table);
                //}

                #region 获取该数据库的所有表

                string sql = "select name from sysobjects where type='V' or type='U' order by name asc";
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    //SqlParameter paramTableName = new SqlParameter("@TableName", SqlDbType.VarChar);
                    //cmd.Parameters.Add(paramTableName);
                    // 读取器
                    SqlDataReader reader = cmd.ExecuteReader();
                    string dicDescription = null;

                    while (reader.Read())
                    {
                        TableModal table = new TableModal();
                        table.TableName = reader[0].ToString();
                        table.DatabaseName = databaseName;
                        // 表描述
                        dicDescription = GetTableDescription(databaseName, table.TableName);
                        table.TableDescription = dicDescription != null
                            ? dicDescription.ToString() :
                            null;
                        lstTables.Add(table.TableName, table);
                    }
                    reader.Close();
                }

                #endregion


                #region 获取引用该表的表名

                // 获取引用该表的表名
                string sqlRefernce = @"select [name] from sysobjects where [id] in 
(select [fkeyid] from sysforeignkeys where [rkeyid] =
(select [id] from sysobjects where [name] = @TableName))";
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlCommand cmdreference = new SqlCommand(sqlRefernce, conn);
                    // 引用当前表的表名参数
                    SqlParameter paramReference = new SqlParameter("@TableName", SqlDbType.VarChar);
                    cmdreference.Parameters.Add(paramReference);
                    // 读取器
                    SqlDataReader readerReference = null;

                    foreach (TableModal table in lstTables.Values)
                    {
                        // 获取引用该表的表名
                        paramReference.Value = table.TableName;
                        readerReference = cmdreference.ExecuteReader();

                        while (readerReference.Read())
                        {
                            string tableName = readerReference["name"].ToString();
                            // 添加到该表的引用表时，复制该对象
                            table.ExtraTable.Add(tableName, (TableModal)lstTables[tableName].Clone());
                        }
                        readerReference.Close();
                    }
                }

                #endregion


                #region 获取引用该表，对应的引用表中的列

                // 获取引用该表，对应的引用表中的列
                string sqlReferenceTableColumn = @"select [name] from syscolumns where [id] = 
(select [id] from sysobjects where [name] = @ReferenceTable) and [colid] in
(select [fkey] from sysforeignkeys where [fkeyid] =
(select [id] from sysobjects where [name] = @ReferenceTable) and [rkeyid] = 
(select [id] from sysobjects where [name] = @TableName))";
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlParameter paramReferenceTable = new SqlParameter("@ReferenceTable", SqlDbType.VarChar);
                    SqlParameter paramTableName = new SqlParameter("@TableName", SqlDbType.VarChar);
                    SqlCommand cmd = new SqlCommand(sqlReferenceTableColumn, conn);
                    cmd.Parameters.Add(paramReferenceTable);
                    cmd.Parameters.Add(paramTableName);

                    string[] tables = new string[lstTables.Count]; ;
                    lstTables.Keys.CopyTo(tables, 0);
                    foreach (string tableName in tables)
                    {
                        TableModal table = lstTables[tableName];
                        if (table.ExtraTable.Count == 0)
                        {
                            continue;
                        }
                        cmd.Parameters["@TableName"].Value = tableName;
                        string[] referenceTables = new string[table.ExtraTable.Count];
                        table.ExtraTable.Keys.CopyTo(referenceTables, 0);
                        foreach (string referenceTable in referenceTables)
                        {
                            cmd.Parameters["@ReferenceTable"].Value = referenceTable;
                            object columnName = cmd.ExecuteScalar();
                            if (columnName != null)
                            {
                                table.ExtraTable[referenceTable].ReferenceColumnName =
                                    columnName.ToString();
                            }
                        }

                    }
                    conn.Close();

                }

                #endregion


                return lstTables;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取该表的列集合
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public IDictionary<string, ColumnModal> GetColumns(string databaseName, string tableName)
        {
            try
            {
                IDictionary<string, ColumnModal> lstColumns = new Dictionary<string, ColumnModal>();
                DataTable dt = new DataTable();

                string sql = String.Format("select top 0 * from {0}", tableName);
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);

                    sda.Fill(dt);
                }
                // 获取描述
                IDictionary<int, string> dicDescription = GetColumnDescription(databaseName, tableName);

                using (SqlConnection conn = (SqlConnection)GetConnection())
                {

                    #region 获取该列的引用表

                    string sqlRefTable = @"select [name] from sysobjects where [id] = 
(select top 1 [rkeyid] from sysforeignkeys where [fkeyid] =
(select [id] from sysobjects where [name] = @TableName) and [fkey] = 
(select [colid] from syscolumns where [name] = @ColumnName and [id] = 
(select [id] from sysobjects where [name] = @TableName)))";
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    // 获取该列的引用表
                    SqlCommand cmdRefTable = new SqlCommand(sqlRefTable, conn);
                    SqlParameter paramRefTable_Table = new SqlParameter("@TableName", SqlDbType.VarChar);
                    paramRefTable_Table.Value = tableName;
                    SqlParameter paramRefTable_Column = new SqlParameter("@ColumnName", SqlDbType.VarChar);

                    cmdRefTable.Parameters.Add(paramRefTable_Table);
                    cmdRefTable.Parameters.Add(paramRefTable_Column);

                    #endregion

                    #region 获取该列的引用表对应的列

                    // 获取该列的引用表对应的列
                    string sqlRefColumn = @"select [name] from syscolumns where [colid] = 
(select top 1 [rkey] from sysforeignkeys where [fkeyid] =
(select [id] from sysobjects where [name] = @TableName) and [fkey] = 
(select [colid] from syscolumns where [name] = @ColumnName and [id] = 
(select [id] from sysobjects where [name] = @TableName)))
and
[id] = 
(select top 1 [rkeyid] from sysforeignkeys where fkeyid =
(select [id] from sysobjects where [name] = @TableName) and [fkey] = 
(select [colid] from syscolumns where [name] = @ColumnName and [id] = 
(select [id] from sysobjects where [name] = @TableName)))";

                    SqlCommand cmdRefColumn = new SqlCommand(sqlRefColumn, conn);
                    SqlParameter paramRefColumn_Table = new SqlParameter("@TableName", SqlDbType.VarChar);
                    paramRefColumn_Table.Value = tableName;
                    SqlParameter paramRefColumn_Column = new SqlParameter("@ColumnName", SqlDbType.VarChar);

                    cmdRefColumn.Parameters.Add(paramRefColumn_Table);
                    cmdRefColumn.Parameters.Add(paramRefColumn_Column);

                    #endregion

                    #region 获得引用该表的外部表名称

                    // 获得引用该表的外部表名称
                    string sqlExtraRefTable = @"select [name] from sysobjects where [id] in 
(select [fkeyid] from sysforeignkeys where [rkeyid] =
(select [id] from sysobjects where [name] = @TableName))";

                    SqlCommand cmdExtraRefTable = new SqlCommand(sqlExtraRefTable, conn);
                    SqlParameter paramExtraRefTable_Table = new SqlParameter("@TableName", SqlDbType.VarChar);

                    #endregion

                    // 获取该列的长度和是否为空
                    IDictionary<int, ColumnModal> dicLengthAndIsNull =
                        GetColumnLengthAndIsNull(databaseName, tableName);

                    foreach (DataColumn col in dt.Columns)
                    {

                        ColumnModal column = new ColumnModal();
                        column.ColumnName = col.ColumnName;
                        column.ColumnType = col.DataType.ToString();
                        column.TableName = tableName;

                        // 获取列名描述
                        column.Description = dicDescription.ContainsKey(col.Ordinal + 1)
                                                ? dicDescription[col.Ordinal + 1]
                                                : String.Empty;

                        column.Length = dicLengthAndIsNull[col.Ordinal + 1].Length;
                        column.IsNull = dicLengthAndIsNull[col.Ordinal + 1].IsNull;

                        // 查询引用表名
                        cmdRefTable.Parameters["@ColumnName"].Value = column.ColumnName;
                        object refenceTableName = cmdRefTable.ExecuteScalar();
                        column.ReferenceTableName =
                            refenceTableName == null ? null : refenceTableName.ToString();

                        // 查询引用表对应的列名
                        cmdRefColumn.Parameters["@ColumnName"].Value = column.ColumnName;
                        object refenceColumnName = cmdRefColumn.ExecuteScalar();
                        column.ReferenceColumnName =
                            refenceColumnName == null ? null : refenceColumnName.ToString();

                        lstColumns.Add(column.ColumnName, column);
                    }
                }

                return lstColumns;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 获取表的描述
        /// </summary>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        private IDictionary<int, string> GetColumnDescription(string databaseName, string tableName)
        {
            try
            {
                IDictionary<int, string> dic = new Dictionary<int, string>();

                string sql = String.Format("select minor_id,value from sys.extended_properties where major_id = (select object_id from sys.objects where [name]='{0}' )", tableName);
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        dic.Add((int)reader["minor_id"], reader["value"].ToString());
                    }
                    reader.Close();
                }

                return dic;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        /// <summary>
        /// 获取列的长度和是否为空
        /// </summary>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        private IDictionary<int, ColumnModal> GetColumnLengthAndIsNull(string databaseName, string tableName)
        {
            try
            {
                IDictionary<int, ColumnModal> dic = new Dictionary<int, ColumnModal>();

                string sql = String.Format(@"select [colId],[length], [isnullable] from syscolumns where [id] = 
(select [id] from sysobjects where [name] = '{0}')", tableName);
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        int columnId = Convert.ToInt32(reader["colId"]);
                        ColumnModal column = new ColumnModal();
                        column.Length = Convert.ToInt32(reader["length"]);
                        column.IsNull = Convert.ToInt32(reader["isnullable"]) == 0 ? true : false;
                        dic.Add(columnId, column);
                    }
                    reader.Close();
                }

                return dic;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 获取表的描述
        /// </summary>
        /// <param name="databaseName">数据库名称</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        private string GetTableDescription(string databaseName, string tableName)
        {
            try
            {
                string tableDescription = null;

                string sql = String.Format("select minor_id,value from sys.extended_properties where major_id = (select object_id from sys.objects where [name]='{0}' ) and minor_id = 0 ", tableName);
                using (SqlConnection conn = (SqlConnection)GetConnection())
                {
                    conn.Open();
                    conn.ChangeDatabase(databaseName);
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        tableDescription = reader["value"].ToString();
                    }
                    reader.Close();
                }

                return tableDescription;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取当前数据库的所有表信息
        /// </summary>
        /// <param name="databaseName">数据库名称</param>
        /// <returns>当前数据库名称的表名集合</returns>
        public IDictionary<string, TableModal> GetTables(string databaseName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}
